Azure develop solutions Relational Database

John

The following is from Azure Developer Training lab for AZ-203

The Azure SQL Database service

SQL Database is a general-purpose relational database managed service in Microsoft Azure that supports structures such as relational data, JSON, spatial, and XML. SQL Database delivers dynamically scalable performance within two different purchasingmodels: a vCore-based purchasing model and a DTU-based purchasing model. SQL Database also provides options such as columnstore indexes for extreme analytic analysis and reporting, and in-memory OLTP for extreme transactional processing. Microsofthandles all patching and updating of the SQL code base seamlessly and abstracts away all management of the underlying infrastructure.

Azure SQL Database provides the following deployment options for an Azure SQL database:

  • As a single database with its own set of resources managed via a logical server
  • As a pooled database in an elastic pool with a shared set of resources managed via a logical server
  • As a part of a collection of databases known as a managed instance that contains system and user databases and sharing a set of resources

The following illustration shows these deployment options:

 

SQL Database shares its code base with the Microsoft SQL Server database engine. With Microsoft’s cloud-first strategy, the newest capabilities of SQL Server are released first to SQL Database, and then to SQL Server itself. This approach provides you with thenewest SQL Server capabilities with no overhead for patching or upgrading – and with these new features tested across millions of databases.

 

Choosing the right SQL Server option in Azure

In Azure, you can have your SQL Server workloads running in a hosted infrastructure (IaaS) or running as a hosted service (PaaS). The key question that you need to ask when deciding between PaaS or IaaS is do you want to manage your database, applypatches, take backups, or you want to delegate these operations to Azure? Depending on the answer, you have the following options:

  • Azure SQL Database: A fully-managed SQL database engine, based on the latest stable Enterprise Edition of SQL Server. This is a relational database-as-a-service (DBaaS) hosted in the Azure cloud that falls into the industry category of Platform-as-a-Service (PaaS). SQL database is built on standardized hardware and software that is owned, hosted, and maintained by Microsoft. With SQL Database, you can use built-in features and functionality that require extensive configuration in SQL Server. Whenusing SQL Database, you pay-as-you-go with options to scale up or out for greater power with no interruption. SQL Database has additional features that are not available in SQL Server, such as built-in intelligence and management. Azure SQL Databaseoffers several deployment options:
    • You can deploy a single database to a logical server. A logical server containing single and pooled databases offers most of database-scoped features of SQL Server. This option is optimized for modern application development of new cloud-bornapplications.
    • You can deploy to a Azure SQL Database Managed Instances. With Azure SQL Database Managed Instance, Azure SQL Database offers shared resources for databases and additional instance-scoped features. Azure SQL Database Managed Instancesupports database migration from on-premises with minimal to no database change. This option provides all of the PaaS benefits of Azure SQL Database but adds capabilities that were previously only available in SQL VMs. This includes a native virtualnetwork (VNet) and near 100% compatibility with on-premises SQL Server.
  • SQL Server on Azure Virtual Machines falls into the industry category Infrastructure-as-a-Service (IaaS) and allows you to run SQL Server inside a fully-managed virtual machine in the Azure cloud. SQL Server virtual machines also run on standardizedhardware that is owned, hosted, and maintained by Microsoft. When using SQL Server on a VM, you can either pay-as you-go for a SQL Server license already included in a SQL Server image or easily use an existing license. You can also stop or resume theVM as needed.SQL Server installed and hosted in the cloud on Windows Server or Linux virtual machines (VMs) running on Azure, also known as an infrastructure as a service (IaaS). SQL Server on Azure virtual machines is a good option for migrating on-premises SQL Server databases and applications without any database change. All recent versions and editions of SQL Server are available for installation in an IaaS virtual machine. The most significant difference from SQL Database is that SQL ServerVMs allow full control over the database engine. You can choose when maintenance/patching will start, to change the recovery model to simple or bulk logged to enable faster load less log, to pause or start engine when needed, and you can fully customizethe SQL Server database engine. With this additional control comes with added responsibility to manage the virtual machines.

 

The main differences between these options are listed in the following table:

SQL Server on VM Azure SQL Database (Managed Instance) Azure SQL Database (Logical server)
You have full control over the SQL Server engine.

Up to 99.95% availability.

Full parity with the matching version of on-premises SQL Server.

Fixed, well-known database engine version.

Easy migration from SQL Server on-premises.

Private IP address within Azure VNet.

You have ability to deploy application or services on the host where SQL Server is placed.

 High compatibility with SQL Server on-premises.

99.99% availability guaranteed.

Built-in backups, patching, recovery.

Latest stable Database Engine version.

Easy migration from SQL Server.

Private IP address within Azure VNet.

Built-in advanced intelligence and security.

Online change of resources (CPU/storage).

 The most commonly used SQL Server features are available.

99.99% availability guaranteed.

Built-in backups, patching, recovery.

Latest stable Database Engine version.

Ability to assign necessary resources (CPU/storage) to individual databases.

Built-in advanced intelligence and security.

Online change of resources (CPU/storage).
You need to manage your backups and patches.

You need to implement your own High-Availability solution.

There is a downtime while changing the resources(CPU/storage)

 There is still some minimal number of SQL Server features that are not available.

No guaranteed exact maintenance time (but nearly transparent).

Compatibility with the SQL Server version can be achieved only using database compatibility levels.

 Migration from SQL Server might be hard.

Some SQL Server features are not available.

No guaranteed exact maintenance time (but nearly transparent).

Compatibility with the SQL Server version can be achieved only using database compatibility levels.

Private IP address cannot be assigned (you can limit the access using firewall rules).

Copy a transactionally consistent copy of an Azure SQL database

Azure SQL Database provides several methods for creating a transactionally consistent copy of an existing Azure SQL database on either the same server or a different server. You can copy a SQL database by using the Azure portal, PowerShell, or T-SQL.

 

Overview

A database copy is a snapshot of the source database as of the time of the copy request. You can select the same server or a different server, its service tier and compute size, or a different compute size within the same service tier (edition). After the copy iscomplete, it becomes a fully functional, independent database. At this point, you can upgrade or downgrade it to any edition. The logins, users, and permissions can be managed independently.

Note: Automated database backups are used when you create a database copy.

 

Logins in the database copy

When you copy a database to the same logical server, the same logins can be used on both databases. The security principal you use to copy the database becomes the database owner on the new database. All database users, their permissions, and their securityidentifiers (SIDs) are copied to the database copy.

When you copy a database to a different logical server, the security principal on the new server becomes the database owner on the new database. If you use contained database users for data access, ensure that both the primary and secondary databases alwayshave the same user credentials, so that after the copy is complete you can immediately access it with the same credentials.

If you use Azure Active Directory, you can completely eliminate the need for managing credentials in the copy. However, when you copy the database to a new server, the login-based access might not work, because the logins do not exist on the new server. Tolearn about managing logins when you copy a database to a different logical server, see How to manage Azure SQL database security after disaster recovery.

After the copying succeeds and before other users are remapped, only the login that initiated the copying, the database owner, can log in to the new database.

 

Copy a database by using the Azure portal

To copy a database by using the Azure portal, open the page for your database, and then click Copy.

 

Copy a database by using PowerShell

To copy a database by using PowerShell, use the New-AzureRmSqlDatabaseCopy cmdlet.

New-AzureRmSqlDatabaseCopy -ResourceGroupName “myResourceGroup” ` -ServerName $sourceserver ` -DatabaseName “MySampleDatabase” ` -CopyResourceGroupName “myResourceGroup” ` -CopyServerName $targetserver ` -CopyDatabaseName “CopyOfMySampleDatabase”

 

Resolve logins

After the new database is online on the destination server, use the ALTER USER statement to remap the users from the new database to logins on the destination server. To resolve orphaned users, see Troubleshoot Orphaned Users.

All users in the new database retain the permissions that they had in the source database. The user who initiated the database copy becomes the database owner of the new database and is assigned a new security identifier (SID). After the copying succeeds andbefore other users are remapped, only the login that initiated the copying, the database owner, can log in to the new database.

 

 

CRUD Operations by Code

Entity Framework

Entity Framework is an object-relational mapper library for Microsoft .NET that is designed to reduce the impedance mismatch between the relational and object-oriented worlds. The goal of the library is to enable developers to interact with data stored inrelational databases by using strongly-typed .NET objects that represent the application’s domain and to eliminate the need for a large portion of the data access “plumbing” code that they usually need to write to access data in a database.

Entity Framework Core and Entity Framework

Entity Framework Core (EF Core) is a recent rewrite of the entire Entity Framework library to target .NET Standard. Entity Framework Core can be used with .NET Framework applications and .NET Core applications. Entity Framework Core was built to bemore lightweight and agile than the full Entity Framework by dropping many of the earlier features from Entity Framework and implementing new, modern, and extensible features at an agile pace. For new applications, we recommend considering using EntityFramework Core over Entity Framework.

Note: The examples in this section will assume that you are using Entity Framework Core.

Entity Framework providers

The Entity Framework provider model allows Entity Framework to be used with different types of database servers. For example, one provider can be plugged in to allow Entity Framework to be used against Microsoft SQL Server, whereas another provider canbe plugged in to allow Entity Framework to be used against Oracle Database. There are many current providers in the market for databases, including:

  • SQL Server
  • SQLite
  • PostgreSQL
  • MySQL
  • MariaDB
  • MyCAT Server
  • SQL Server Compact
  • Firebird
  • DB2
  • Informix
  • Oracle
  • Microsoft Access

Note: If a provider you need is not available, you can certainly write a provider yourself, although it should not be considered a trivial undertaking.

Entity Framework Core also ships with an InMemory provider. This database provider allows Entity Framework Core to be used with an in-memory database. The InMemory provider is useful when you want to test components by using something thatapproximates connecting to the real database without the overhead of actual database operations.

Note: EF Core database providers do not have to be relational databases. InMemory is designed to be a general-purpose database for testing and is not designed to mimic a relational database.

SQL Server provider

This database provider allows Entity Framework Core to be used with Microsoft SQL Server (including Microsoft Azure SQL Database). The provider is maintained as an open-source project as part of the Entity Framework Core repository on GitHub (https://github.com/aspnet/EntityFrameworkCore).

Many of the examples you will find online assume that you are using the SQL Server provider with Entity Framework Core. It is important to remember that Entity Framework Core has a provider model that abstracts the underlying database away from the actualdatabase access logic. The code samples you see can be used with many of the database providers.

MySQL and PostgreSQL providers

The MySQL team maintains a database provider for both Entity Framework and Entity Framework Core as part of the MySQL Connector for .NET library. Along with the MySQL team, other third-party groups have written providers for MySQL. Two of theMySQL providers are:

There are multiple third-party organizations that have written .NET libraries to access PostgreSQL. Many of them have rewritten their Entity Framework providers to support Entity Framework Core. These libraries include:

These providers allow you to use Entity Framework Core with a MySQL or PostgreSQL database in the same manner as you would use the library with a SQL database.

Modeling a database by using Entity Framework Core

To use Entity Framework to query, insert, update, and delete data using .NET objects, you first need to create a model that maps the entities and relationships defined in your model to tables in a database.

Entity Framework uses a set of conventions to build a model based on the shapes of your entity classes. You can specify additional configuration to supplement and override what was discovered by convention. The conventions can be applied to a model targetingany data store and when targeting any relational database. Providers might also enable a configuration that is specific to a particular data store.

First, let’s look at how we can model a database with a single table named Blogs.

BlogId Url Description
1 /first-post This is my first post on this platform
2 /follow-up-post NULL

If we want to use plain-old CLR objects (POCOs), such as existing domain objects, to model this table, we would have a class that looks like this:

public class Blog { public int BlogId { get; set; } public string Url { get; set; } public string Description { get; set; } }

Logically, our database has a table that is a collection of these blog instances. Without knowing anything about Entity Framework, we would probably create a class that looks like this:

public class BlogDatabase { public IEnumerable<Blog> Blogs { get; set; } }

Now, we need to find a way to mark these classes in C# as models of our database. Including a type in the model means that Entity Framework has metadata about that type and will attempt to read and write instances from and to the database. There are two methods for modeling a database: the fluent API or data annotations.

Fluent API

You can override the OnModelCreating method in your derived context class and use the ModelBuilder API to configure your model. This is the most powerful method of configuration and allows the configuration to be specified without modifying your entity classes. The fluent API configuration has the highest precedence and will override conventions and data annotations:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Blog>() .HasKey(c => c.BlogId) .Property(b => b.Url) .IsRequired() .Property(b => b.Description); }

Data annotations

You can apply attributes (known as data annotations) to your classes and properties. data annotations will override conventions but will be overwritten by a fluent API configuration (if it exists):

public class Blog { [Key] public int BlogId { get; set; } [Required] public string Url { get; set; } public string Description { get; set; } }

DbContext implementation

After you have a model, the primary class your application interacts with is System.Data.Entity.DbContext (often referred to as the context class). You can use a DbContext class associated to a model to:

  • Write and execute queries.
  • Materialize query results as entity objects.
  • Track changes that are made to those objects.
  • Persist object changes back on the database.
  • Bind objects in memory to UI controls.

The recommended way to work with the context is to define a class that derives from DbContext and exposes DbSet properties that represent collections of the specified entities in the context:

public class BlogContext : DbContext { public DbSet<Blog> Blogs { get; set; } }

By convention, types that are exposed in DbSet properties on your context are included in your model. In addition, types that are mentioned in the OnModelCreating method are also included. Finally, any types that are found by recursively exploring thenavigation properties of discovered types are also included in the model.

 

Querying databases by using Entity Framework Core

The DbSet<> generic class includes methods that will allow you to query your database by using language-integrated query (LINQ).

If you are already familiar with the LINQ syntax, you can perform many queries in Entity Framework Core without the need to learn too much. This is because the DbSet<> generic class implements the IEnumerable<> interface, giving you access to many of the existing LINQ queries.

For example, you can load all the data from a table by enumerating the collection with a call to the ToList method:

List<Blog> all blogs = context.Blogs.ToList();

You can use LINQ methods such as the Where method to filter your resulting list:

IEnumerable<Blog> someblogs = context.Blogs .Where(b => b.Url.Contains(“dotnet”))

You can also use the Single method to get a single instance that matches a specific filter:

Blog specific blog = context.Blogs .Single(b => b.BlogId == 1);

When you call LINQ operators, you are simply building up an in-memory representation of the query. The query is sent to the database only when the results are consumed (enumerated). The most common operations that result in the query being sent to the database are:

  • Iterating the results in a for loop.
  • Using an operator such as ToList, ToArray, Single, or Count.
  • Data binding the results of a query to a UI.

Although Entity Framework does help protect against SQL injection attacks, it does not do any general validation of input. Therefore, if values being passed to APIs, used in LINQ queries, assigned to entity properties, and so on come from an untrusted source, theappropriate validation per your application requirements should be performed. This includes any user input used to dynamically construct queries. Even when using LINQ, if you are accepting user input to build expressions, you need to make sure that onlyintended expressions can be constructed.

 

 

References